package com.myMinistry.provider;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.os.Environment;

import com.myMinistry.Helper;
import com.myMinistry.provider.MinistryContract.EntryType;
import com.myMinistry.provider.MinistryContract.Householder;
import com.myMinistry.provider.MinistryContract.Joins;
import com.myMinistry.provider.MinistryContract.Literature;
import com.myMinistry.provider.MinistryContract.LiteraturePlaced;
import com.myMinistry.provider.MinistryContract.LiteratureType;
import com.myMinistry.provider.MinistryContract.Publisher;
import com.myMinistry.provider.MinistryContract.Qualified;
import com.myMinistry.provider.MinistryContract.Rollover;
import com.myMinistry.provider.MinistryContract.Time;
import com.myMinistry.provider.MinistryDatabase.Tables;

public class MinistryService {
	public static final int ACTIVE = 1;
	public static final int INACTIVE = 0;
	private final Context context;
    private static SQLiteDatabase sqlDB;
    
    public MinistryService(Context context) {
    	this.context = context;
    }

	public MinistryService openReadable() throws SQLException {
		sqlDB = new MinistryDatabase(context).getReadableDatabase();
		return this;
    }

	public MinistryService openWritable() throws SQLException {
		sqlDB = new MinistryDatabase(context).getWritableDatabase();
    	return this;
    }
    
    public boolean isOpen() {
    	return sqlDB.isOpen();
    }
    
    public void close() {
    	if(sqlDB != null && isOpen())
    		sqlDB.close();
    }
	
	public Cursor fetchRecord(String Table, String[] cols, int rowID) {
		return sqlDB.query(Table, cols, "_id = " + rowID, null, null, null, null, "1");
	}
    
    public Cursor fetchRecords(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)
    {
    	return sqlDB.query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
	}
    
    public int remove(String Table, int rowID) {
		return sqlDB.delete(Table, "_id = " + rowID, null);
    }
    
    public long create(String Table, ContentValues initialValues) {
    	return sqlDB.insert(Table, null, initialValues);
    }
    
    public int update(String Table, long rowId, ContentValues args) {
		return sqlDB.update(Table, args, "_id = " + rowId, null);
    }
    
	public Cursor fetchActivePublishers() {
		return sqlDB.query(Tables.PUBLISHERS, new String[] {Publisher._ID, Publisher.NAME, Publisher.ACTIVE }, Publisher.ACTIVE + " = 1", null, null, null, Publisher.NAME, null);
    }
    
	public Cursor fetchInactivePublishers() {
		return sqlDB.query(Tables.PUBLISHERS, new String[] {Publisher._ID, Publisher.NAME, Publisher.ACTIVE }, Publisher.ACTIVE + " = 0", null, null, null, Publisher.NAME, null);
    }
    
	public Cursor fetchAllPublishers() {
		return sqlDB.query(Tables.PUBLISHERS, new String[] {Publisher._ID, Publisher.NAME, Publisher.ACTIVE }, null, null, null, null, Publisher.ACTIVE + " DESC, " + Publisher.NAME, null);
    }
	
	public int fetchBooksPlacedCountForPublisher(String formattedDate, String timeFrame, int publisherID) {
		int retVal = 0;
    	String sql	=	"SELECT SUM(" + Qualified.PLACED_LITERATURE_COUNT + " * " + Qualified.LITERATURE_WEIGHT + ") AS " + LiteraturePlaced.COUNT
    					+ " FROM " + Tables.PLACED_LITERATURE
    					+ Joins.LITERATURE_JOIN_PLACED_LITERATURE
    					+ Joins.TYPE_LITERATURE_JOIN_LITERATURE
    					+ " WHERE " + Qualified.TYPE_OF_LITERATURE_ID + " = 1"
    					+ " AND " + Qualified.PLACED_LITERATURE_PUBLISHER_ID + " = " + publisherID
    					+ " AND date(" + Qualified.PLACED_LITERATURE_DATE + ") >= date('" + formattedDate + "','start of month')"
    					+ " AND date(" + Qualified.PLACED_LITERATURE_DATE + ") < date('" + formattedDate + "','start of month','+1 " + timeFrame + "')";
    	
    	Cursor record = sqlDB.rawQuery(sql, null);
    	if(record.moveToFirst())
			retVal = record.getInt(0); /** Returns first column, which is the sum. */ 
    	if(record != null && !record.isClosed())
    		record.close();
		return retVal;
	}
	
	public int fetchBrochuresPlacedCountForPublisher(String formattedDate, String timeFrame, int publisherID) {
		int retVal = 0;
    	String sql	=	"SELECT SUM(" + Qualified.PLACED_LITERATURE_COUNT + " * " + Qualified.LITERATURE_WEIGHT + ") AS " + LiteraturePlaced.COUNT
    					+ " FROM " + Tables.PLACED_LITERATURE
    					+ Joins.LITERATURE_JOIN_PLACED_LITERATURE
    					+ Joins.TYPE_LITERATURE_JOIN_LITERATURE
    					+ " WHERE " + Qualified.TYPE_OF_LITERATURE_ID + " = 2"
    					+ " AND " + Qualified.PLACED_LITERATURE_PUBLISHER_ID + " = " + publisherID
    					+ " AND date(" + Qualified.PLACED_LITERATURE_DATE + ") >= date('" + formattedDate + "','start of month')"
    					+ " AND date(" + Qualified.PLACED_LITERATURE_DATE + ") < date('" + formattedDate + "','start of month','+1 " + timeFrame + "')";
    	
    	Cursor record = sqlDB.rawQuery(sql, null);
    	if(record.moveToFirst())
			retVal = record.getInt(0); /** Returns first column, which is the sum. */ 
    	if(record != null && !record.isClosed())
    		record.close();
		return retVal;
	}
	
	public int fetchMagazinesPlacedCountForPublisher(String formattedDate, String timeFrame, int publisherID) {
		int retVal = 0;
    	String sql	=	"SELECT SUM(" + Qualified.PLACED_LITERATURE_COUNT + " * " + Qualified.LITERATURE_WEIGHT + ") AS " + LiteraturePlaced.COUNT
    					+ " FROM " + Tables.PLACED_LITERATURE
    					+ Joins.LITERATURE_JOIN_PLACED_LITERATURE
    					+ Joins.TYPE_LITERATURE_JOIN_LITERATURE
    					+ " WHERE " + Qualified.TYPE_OF_LITERATURE_ID + " = 3"
    					+ " AND " + Qualified.PLACED_LITERATURE_PUBLISHER_ID + " = " + publisherID
    					+ " AND date(" + Qualified.PLACED_LITERATURE_DATE + ") >= date('" + formattedDate + "','start of month')"
    					+ " AND date(" + Qualified.PLACED_LITERATURE_DATE + ") < date('" + formattedDate + "','start of month','+1 " + timeFrame + "')";
    	
    	Cursor record = sqlDB.rawQuery(sql, null);
    	if(record.moveToFirst())
			retVal = record.getInt(0); /** Returns first column, which is the sum. */ 
    	if(record != null && !record.isClosed())
    		record.close();
		return retVal;
	}
    
    public int fetchReturnVisitCountForPublisher(String formattedDate, String timeFrame, int publisherID) {
		int retVal = 0;
    	String sql	=	"SELECT COUNT(" + Time._ID + ")"
    					+ " FROM " + Tables.TIMES
    					+ " WHERE " + Time.ENTRY_TYPE_ID + " IN ("
					    											+ " SELECT " + EntryType._ID
					    											+ " FROM " + Tables.ENTRY_TYPES
					    											+ " WHERE " + EntryType.STUDY + " = 1"
					    											+ " OR " + EntryType.RV + " = 1"
					    											+ ")"
					    + " AND " + Time.PUBLISHER_ID + " = " + publisherID
    					+ " AND date(" + Time.DATE + ") >= date('" + formattedDate + "','start of month')"
    					+ " AND date(" + Time.DATE + ") < date('" + formattedDate + "','start of month','+1 " + timeFrame + "')";

    	String sql2	=	"SELECT SUM(" + Time.RETURN_VISITS + ")"
    					+ " FROM " + Tables.TIMES
    					+ " WHERE " + Time.PUBLISHER_ID + " = " + publisherID
    					+ " AND date(" + Time.DATE + ") >= date('" + formattedDate + "','start of month')"
    					+ " AND date(" + Time.DATE + ") < date('" + formattedDate + "','start of month','+1 " + timeFrame + "')";
    	
    	Cursor record1 = sqlDB.rawQuery(sql, null);
    	Cursor record2 = sqlDB.rawQuery(sql2, null);
    	
    	if(record1.moveToFirst())
			retVal += record1.getInt(0); /** Returns first column, which is the count. */
    	if(record2.moveToFirst())
			retVal += record2.getInt(0); /** Returns first column, which is the count. */
    	
    	if(record1 != null && !record1.isClosed())
    		record1.close();
    	if(record2 != null && !record2.isClosed())
    		record2.close();
    	
    	return retVal;
	}
	
	public int fetchStudyCountForPublisher(String formattedDate, String timeFrame, int publisherID) {
		int retVal = 0;
		String sql	=	"SELECT " + Time._ID
						+ " FROM " + Tables.TIMES
						+ " WHERE " + Time.ENTRY_TYPE_ID + " IN ("
																	+ " SELECT " + EntryType._ID
																	+ " FROM " + Tables.ENTRY_TYPES
																	+ " WHERE " + EntryType.STUDY + " = 1"
																	+ ")"
						+ " AND " + Time.PUBLISHER_ID  + " = " + publisherID
						+ " AND date(" + Time.DATE + ") >= date('" + formattedDate + "','start of month')"
    					+ " AND date(" + Time.DATE + ") < date('" + formattedDate + "','start of month','+1 " + timeFrame + "')"
						+ " GROUP BY " + Time.HOUSEHOLDER_ID;
		
		Cursor record = sqlDB.rawQuery(sql, null);
		retVal = record.getCount();
		if(record != null && !record.isClosed())
			record.close();
		return retVal;
	}
	
	public double fetchHoursForMonthForPublisher(String formattedDate, int publisherID) {
		double retVal = 0;
    	String sql1 =	"SELECT SUM(" + Time.TOTAL_HOURS + ") FROM " + Tables.TIMES
    					+ " INNER JOIN " + Tables.ENTRY_TYPES + " ON " + Qualified.ENTRY_TYPE_ID + " = " + Qualified.TIME_ENTRY_TYPE_ID
    					+ " WHERE date(" + Time.DATE + ") >= date('" + formattedDate + "','start of month')"
    					+ " AND date(" + Time.DATE + ") < date('" + formattedDate + "','start of month','+1 month')"
    					+ " AND " + Qualified.TIME_PUBLISHER_ID + " = " + publisherID
    					+ " AND " + Qualified.ENTRY_TYPE_RBC + " <> 1";
    	/** left outer join? */
    	
    	String sql2 =	"SELECT SUM(" + Time.TOTAL_HOURS + ") FROM " + Tables.TIMES
						+ " WHERE date(" + Time.DATE + ") >= date('" + formattedDate + "','start of month')"
						+ " AND date(" + Time.DATE + ") < date('" + formattedDate + "','start of month','+1 month')"
						+ " AND " + Time.PUBLISHER_ID + " = " + publisherID
						+ " AND " + Time.ENTRY_TYPE_ID + " = 0";
    	
    	Cursor cursor1 = sqlDB.rawQuery(sql1, null);
    	Cursor cursor2 = sqlDB.rawQuery(sql2, null);
    	
    	if(cursor1.moveToFirst())
    		retVal += cursor1.getDouble(0);
    	if(cursor2.moveToFirst())
    		retVal += cursor2.getDouble(0);
    	
    	if(cursor1 != null && !cursor1.isClosed())
    		cursor1.close();
    	if(cursor2 != null && !cursor2.isClosed())
    		cursor2.close();
    	
    	return retVal;
    }
	
	public double fetchHoursForYearForPublisher(String formattedDate, int publisherID) {
		double retVal = 0;
    	String sql1 =	"SELECT SUM(" + Time.TOTAL_HOURS + ") FROM " + Tables.TIMES
    					+ " LEFT OUTER JOIN " + Tables.ENTRY_TYPES + " ON " + Qualified.ENTRY_TYPE_ID + " = " + Qualified.TIME_ENTRY_TYPE_ID
    					+ " WHERE date(" + Time.DATE + ") >= date('" + formattedDate + "','start of month')"
    					+ " AND date(" + Time.DATE + ") < date('" + formattedDate + "','start of month','+1 year')"
    					+ " AND " + Qualified.TIME_PUBLISHER_ID + " = " + publisherID
    					+ " AND " + EntryType.RBC + " <> 1";
    	
    	
    	String sql2 =	"SELECT SUM(" + Time.TOTAL_HOURS + ") FROM " + Tables.TIMES
						+ " WHERE date(" + Time.DATE + ") >= date('" + formattedDate + "','start of month')"
						+ " AND date(" + Time.DATE + ") < date('" + formattedDate + "','start of month','+1 year')"
						+ " AND " + Time.PUBLISHER_ID + " = " + publisherID
						+ " AND " + Time.ENTRY_TYPE_ID + " = 0";
    	
    	Cursor cursor1 = sqlDB.rawQuery(sql1, null);
    	Cursor cursor2 = sqlDB.rawQuery(sql2, null);
    	
    	if(cursor1.moveToFirst())
    		retVal += cursor1.getDouble(0);
    	if(cursor2.moveToFirst())
    		retVal += cursor2.getDouble(0);
    	
    	if(cursor1 != null && !cursor1.isClosed())
    		cursor1.close();
    	if(cursor2 != null && !cursor2.isClosed())
    		cursor2.close();
    	
    	return retVal;
    }
	
	public double fetchRBCHoursForMonthForPublisher(String formattedDate, int publisherID) {
		double retVal = 0;
    	String sql =	"SELECT SUM(" + Time.TOTAL_HOURS + ") FROM " + Tables.TIMES
    					+ " INNER JOIN " + Tables.ENTRY_TYPES + " ON " + Qualified.ENTRY_TYPE_ID + " = " + Qualified.TIME_ENTRY_TYPE_ID
    					+ " WHERE date(" + Time.DATE + ") >= date('" + formattedDate + "')"
    					+ " AND date(" + Time.DATE + ") < date('" + formattedDate + "','start of month','+1 month')"
    					+ " AND " + Qualified.ENTRY_TYPE_RBC + " = 1";
    	
    	Cursor cursor = sqlDB.rawQuery(sql, null);
    	if(cursor.moveToFirst())
    		retVal = cursor.getDouble(0); /** Returns First Column - SUM() */
    	if(cursor != null && !cursor.isClosed())
    		cursor.close();
    	return retVal;
    }

	public double fetchRBCHoursForYearForPublisher(String formattedDate, int publisherID) {
		double retVal = 0;
    	String sql =	"SELECT SUM(" + Time.TOTAL_HOURS + ") FROM " + Tables.TIMES
    					+ " INNER JOIN " + Tables.ENTRY_TYPES + " ON " + Qualified.ENTRY_TYPE_ID + " = " + Qualified.TIME_ENTRY_TYPE_ID
    					+ " WHERE date(" + Time.DATE + ") >= date('" + formattedDate + "')"
    					+ " AND date(" + Time.DATE + ") < date('" + formattedDate + "','start of month','+1 year')"
    					+ " AND " + Qualified.ENTRY_TYPE_RBC + " = 1";
    	
    	Cursor cursor = sqlDB.rawQuery(sql, null);
    	if(cursor.moveToFirst())
    		retVal = cursor.getDouble(0); /** Returns First Column - SUM() */
    	if(cursor != null && !cursor.isClosed())
    		cursor.close();
    	return retVal;
    }
	
	public Cursor fetchPublisher(int _id) {
		return sqlDB.query(Tables.PUBLISHERS
				,new String[] {Publisher._ID,Publisher.NAME,Publisher.ACTIVE}
				,Publisher._ID + " = " + _id
				,null
				,null
				,null
				,null
				,"1");
	}
	
	public long createPublisher(ContentValues values) {
    	return sqlDB.insert(Tables.PUBLISHERS,null,values);
    }
	
	public void updatePublisher(int _id, ContentValues values) {
		sqlDB.update(Tables.PUBLISHERS,values,Publisher._ID + " = " + _id,null);
	}
    
	public Cursor fetchActiveHouseholders() {
		return sqlDB.query(Tables.HOUSEHOLDERS, new String[] {Householder._ID, Householder.NAME}, Householder.ACTIVE + " = 1", null, null, null, Householder.DEFAULT_SORT, null);
    }
    
	public Cursor fetchInactiveHouseholders() {
		return sqlDB.query(Tables.HOUSEHOLDERS, new String[] {Householder._ID, Householder.NAME}, Householder.ACTIVE + " = 0", null, null, null, Householder.DEFAULT_SORT, null);
    }
    
	public Cursor fetchAllHouseholders() {
		return sqlDB.query(Tables.HOUSEHOLDERS, new String[] {Householder._ID, Householder.NAME, Householder.ACTIVE}, null, null, null, null, Householder.DEFAULT_SORT, null);
    }
	
	public Cursor fetchHouseholder(int _id) {
		return sqlDB.query(Tables.HOUSEHOLDERS
				,new String[] {Householder._ID,Householder.NAME,Householder.ADDR,Householder.MOBILE_PHONE,Householder.HOME_PHONE,Householder.WORK_PHONE,Householder.OTHER_PHONE,Householder.ACTIVE,Householder.STAR,Householder.ENTRY_TYPE_ID,Householder.STUDY,Householder.RV}
				,Householder._ID + " = " + _id
				,null
				,null
				,null
				,null
				,"1");
	}
	
	public long createHouseholder(ContentValues values) {
    	return sqlDB.insert(Tables.HOUSEHOLDERS,null,values);
    }
	
	public void updateHouseholder(int _id, ContentValues values) {
		sqlDB.update(Tables.HOUSEHOLDERS,values,Householder._ID + " = " + _id,null);
	}
    
    public Cursor fetchTimeEntry(int _id) {
    	return sqlDB.query(Tables.TIMES
				,new String[] {Time._ID,Time.PUBLISHER_ID,Time.HOUSEHOLDER_ID,Time.ENTRY_TYPE_ID,Time.DATE,Time.TIME_START,Time.TIME_END,Time.TOTAL_HOURS,Time.RETURN_VISITS}
				,Time._ID + " = " + _id
				,null
				,null
				,null
				,null
				,"1");
    }
    
    public Cursor fetchEntryTypes() {
		return sqlDB.query(Tables.ENTRY_TYPES, new String[] {EntryType._ID,EntryType.NAME,EntryType.RV,EntryType.STUDY}, EntryType.ACTIVE + " = 1", null, null, null, EntryType.DEFAULT_SORT, null);
    }
    
    public Cursor fetchEntryTypesForHouseholders() {
		return sqlDB.query(Tables.ENTRY_TYPES, new String[] {EntryType._ID,EntryType.NAME,EntryType.RV,EntryType.STUDY}, EntryType.ACTIVE + " = 1 AND (" + EntryType.STUDY + "=1 OR " + EntryType.RV + "=1)", null, null, null, EntryType.DEFAULT_SORT, null);
    }
    
    public Cursor fetchEntryType(int _id) {
    	return sqlDB.query(Tables.ENTRY_TYPES
				,new String[] {EntryType._ID,EntryType.PUBLISHER_ID,EntryType.NAME,EntryType.ACTIVE,EntryType.RBC,EntryType.RV,EntryType.STUDY,EntryType.SORT_ORDER}
				,EntryType._ID + " = " + _id
				,null
				,null
				,null
				,null
				,"1");
    }
	
	public long createTimeEntry(ContentValues values) {
    	return sqlDB.insert(Tables.TIMES,null,values);
    }
	
	public void updateTimeEntry(int _id, ContentValues values) {
		sqlDB.update(Tables.TIMES,values,Time._ID + " = " + _id,null);
	}
    
    public Cursor fetchTypesOfLiterature() {
		return sqlDB.query(Tables.TYPES_OF_LIERATURE, new String[] {LiteratureType._ID,LiteratureType.NAME}, LiteratureType.ACTIVE + " = 1", null, null, null, LiteratureType.DEFAULT_SORT, null);
    }
    
    public Cursor fetchTypeOfLiterature(int _id) {
    	return sqlDB.query(Tables.TYPES_OF_LIERATURE
				,new String[] {LiteratureType._ID,LiteratureType.PUBLISHER_ID,LiteratureType.NAME,LiteratureType.ACTIVE}
				,LiteratureType._ID + " = " + _id
				,null
				,null
				,null
				,null
				,"1");
    }
    
    public Cursor fetchLiteratureByType(int _typeID) {
    	return sqlDB.query(Tables.LITERATURE, new String[] {Literature._ID,Literature.NAME}, Literature.ACTIVE + " = 1 AND " + Literature.TYPE_OF_LIERATURE_ID + " = " + _typeID, null, null, null, Literature.DEFAULT_SORT, null);
    }
    
    public Cursor fetchLiteratureByID(int _id) {
    	return sqlDB.query(Tables.LITERATURE, new String[] {Literature._ID,Literature.NAME,Literature.TYPE_OF_LIERATURE_ID,Literature.ACTIVE,Literature.WEIGHT}, Literature._ID + " = " + _id, null, null, null, null, null);
    }
    
    public Cursor fetchAllLiteratureByType(int _typeID) {
    	return sqlDB.query(Tables.LITERATURE, new String[] {Literature._ID,Literature.NAME,Literature.ACTIVE}, Literature.TYPE_OF_LIERATURE_ID + " = " + _typeID, null, null, null, Literature.DEFAULT_SORT, null);
    }
	
	public long createLiterature(ContentValues values) {
    	return sqlDB.insert(Tables.LITERATURE,null,values);
    }
	
	public void updateLiterature(int _id, ContentValues values) {
		sqlDB.update(Tables.LITERATURE,values,Literature._ID + " = " + _id,null);
	}
    
    public Cursor fetchRolloverMinutes(int publisherID, String date) {
    	return sqlDB.query(Tables.ROLLOVER, new String[] {Rollover._ID,Rollover.PUBLISHER_ID,Rollover.DATE,Rollover.MINUTES}, Rollover.PUBLISHER_ID + " = " + publisherID + " AND " + Rollover.DATE + " = '" + date + "'", null, null, null, null, "1");
    }
	
	public long createRolloverMinutes(ContentValues values) {
    	return sqlDB.insert(Tables.ROLLOVER,null,values);
    }
	
	public void updateRolloverMinutes(int _id, ContentValues values) {
		sqlDB.update(Tables.ROLLOVER,values,Rollover._ID + " = " + _id,null);
	}
	
	public void removeRolloverMinutes(int _id) {
		sqlDB.delete(Tables.ROLLOVER, Rollover._ID + " = " + _id, null);
	}
	
	public Cursor fetchMostRecentRolloverDate(int publisherID) {
		return sqlDB.query(Tables.ROLLOVER, new String[] {Rollover._ID,Rollover.PUBLISHER_ID,Rollover.DATE,Rollover.MINUTES}, Rollover.PUBLISHER_ID + " = " + publisherID, null, null, null, "date(" + Rollover.DATE + ") DESC", "1");
	}
    
    public Cursor fetchRolloverTimeEntry(String formattedDate, int publisherID) {
    	return sqlDB.query(Tables.TIMES
				,new String[] {Time._ID,Time.PUBLISHER_ID,Time.HOUSEHOLDER_ID,Time.ENTRY_TYPE_ID,Time.DATE,Time.TIME_START,Time.TIME_START,Time.TOTAL_HOURS,Time.RETURN_VISITS}
				,Time.PUBLISHER_ID + " = " + publisherID + " AND " + Time.ENTRY_TYPE_ID + " = " + EntryType.ROLLOVER_ID + " AND " + Time.DATE + " = '" + formattedDate + "'"
				,null
				,null
				,null
				,null
				,"1");
    }
	
	public void removeTimeEntry(int _id) {
		sqlDB.delete(Tables.TIMES, Time._ID + " = " + _id, null);
	}
	
	public long createEntryType(ContentValues values) {
    	return sqlDB.insert(Tables.ENTRY_TYPES,null,values);
    }
	
	public long createLiteratureType(ContentValues values) {
    	return sqlDB.insert(Tables.TYPES_OF_LIERATURE,null,values);
    }
	
	public Cursor fetchTimeEntriesByPublisherAndMonth(int publisherID, String date) {
		String sql = 	"SELECT " + Qualified.TIME_ID
						+ "," + Qualified.TIME_DATE
						+ "," + Qualified.TIME_TIME_START
						+ "," + Qualified.TIME_TIME_END
						+ "," + Qualified.TIME_TOTAL_HOURS
						+ "," + Qualified.TIME_RVs
						+ "," + Qualified.ENTRY_TYPE_NAME
						+ "," + Qualified.HOUSEHOLDER_NAME
						+ " FROM " + Tables.TIMES
						+ " LEFT OUTER JOIN " + Tables.ENTRY_TYPES + " ON " + Qualified.ENTRY_TYPE_ID + " = " + Qualified.TIME_ENTRY_TYPE_ID
						+ " LEFT OUTER JOIN " + Tables.HOUSEHOLDERS + " ON " + Qualified.HOUSEHOLDER_ID + " = " + Qualified.TIME_HOUSEHOLDER_ID
						+ " WHERE date(" + Time.DATE + ") >= date('" + date + "','start of month')"
    					+ " AND date(" + Time.DATE + ") < date('" + date + "','start of month','+1 month')"
    					+ " AND time." + Time.PUBLISHER_ID + " = " + publisherID
    					+ " ORDER BY date(" + Time.DATE + ") DESC, time(" + Time.TIME_END + ") DESC";
		
		return sqlDB.rawQuery(sql, null);
	}
	
	public Cursor fetchLiteratureByTimeID(int timeID) {
		String sql =	"SELECT " + Qualified.PLACED_LITERATURE_ID
						+ "," + Qualified.PLACED_LITERATURE_PUBLISHER_ID
						+ "," + Qualified.PLACED_LITERATURE_LIT_ID
						+ "," + Qualified.PLACED_LITERATURE_HOUSEHOLDER_ID
						+ "," + Qualified.PLACED_LITERATURE_COUNT
						+ "," + Qualified.LITERATURE_NAME
						+ " FROM " + Tables.PLACED_LITERATURE
						+ Joins.LITERATURE_JOIN_PLACED_LITERATURE
						+ " WHERE " + Qualified.PLACED_LITERATURE_TIME_ID + "=" + timeID;
		return sqlDB.rawQuery(sql, null);
	}
	
	public long createPlacedLiterature(ContentValues values) {
    	return sqlDB.insert(Tables.PLACED_LITERATURE,null,values);
    }
	
	public void updatePlacedLiterature(int _id, ContentValues values) {
		sqlDB.update(Tables.PLACED_LITERATURE,values,LiteraturePlaced._ID + " = " + _id,null);
	}
	
	public void deletePlacedLiteratureByTimeAndIDs(String _saveIDs, int _timeID) {
		sqlDB.delete(Tables.PLACED_LITERATURE, LiteraturePlaced.TIME_ID + " = " + _timeID + " AND " + LiteraturePlaced._ID + " NOT IN (" + _saveIDs + ")", null);
	}
    
    public void deleteTimeByID(int rowID) {
		sqlDB.delete(Tables.TIMES, "_id = " + rowID, null);
		sqlDB.delete(Tables.PLACED_LITERATURE, LiteraturePlaced.TIME_ID + "=" + rowID, null);
    }
    
    public boolean importDatabase(String dbPath, String packageName) throws IOException {	
    	/** Close the SQLiteOpenHelper so it will commit the created empty database to internal storage. */
    	close();
    	File newDb = new File(dbPath);
    	File oldDb = new File(Environment.getDataDirectory(), "/data/" + packageName + "/databases/" + MinistryDatabase.DATABASE_NAME);
    	
    	if (newDb.exists()) {
    		Helper.copyFile(new FileInputStream(newDb), new FileOutputStream(oldDb));
    		/** Access the copied database so SQLiteHelper will cache it and mark it as created. */
    		sqlDB.close();
    		//getWritableDatabase().close();
    		return true;
    	}
    	else
    		return false;
    }
}